/**
 * 
 */
package com.dsbridge.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.stereotype.Repository;

import com.dsbridge.bank.model.DBUtils;
import com.dsbridge.bank.model.Payment;

/**
 * @author Naveen
 * 
 */
@Repository
public class PaymentDAO {

	public int insert(Payment p) {
		int auto_id = 0;
		PreparedStatement ps = null;
		String sql = "INSERT INTO PAYMENT(PAYMENT_STATUS,TAX_ID,AMOUNT) VALUES(?,?,?) ";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			try {
				ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

				// populate the data

				ps.setString(1, p.getPaymentstatus());
				ps.setInt(2, p.getTaxid());
				ps.setDouble(3, p.getAmount());

				// Execute the statements
				ps.executeUpdate();
				boolean returnLastInsertId = true;
				if (returnLastInsertId) {
					ResultSet rs = null;

					rs = ps.getGeneratedKeys();

					rs.next();
					auto_id = rs.getInt(1);
					// TODO Auto-generated catch block

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {

				DBUtils.closeConnection();
			}

		} else {
			System.out.println("Unable to get the connection");
		}

		return auto_id;

	}

	public int update(Payment p) throws SQLException {
		int result = 0;
		PreparedStatement ps = null;
		String sql = "UPDATE PAYMENT SET  AMOUNT = ? WHERE TAX_ID = ? ";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = con.prepareStatement(sql);

			// Populate the data

			ps.setDouble(1, p.getAmount());
			ps.setInt(2, p.getTaxid());
			// Execute the statements
			result = ps.executeUpdate();

		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();

		return result;

	}

	public int delete(Payment p) throws SQLException {

		int result = 0;
		PreparedStatement ps = null;
		String sql = "DELETE FROM PAYMENT WHERE TAX_ID = ?";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = con.prepareStatement(sql);
			ps.setInt(1, p.getTaxid());

			result = ps.executeUpdate();
		} else {
			System.out.println("Unable to get the connection");
		}

		ps.close();
		DBUtils.closeConnection();

		return result;

	}

	public Payment retrievePaymentbyTaxid(int taxid) throws SQLException {

		Payment p = new Payment();
		PreparedStatement ps = null;
		String sql = "SELECT * FROM PAYMENT WHERE TAX_ID = ?";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = con.prepareStatement(sql);
			// populate the data
			ps.setInt(1, taxid);
			// Execute the statements by passing the taxid
			ResultSet result = ps.executeQuery();

			if (result.next()) {
				int paymenttransId = result.getInt("PAYMENT_TRANS_ID");
				String paymentStatus = result.getString("PAYMENT_STATUS");
				int taxId = result.getInt("TAX_ID");
				double amount = result.getDouble("AMOUNT");

				p.setPaymenttransid(paymenttransId);
				p.setPaymentstatus(paymentStatus);
				p.setTaxid(taxId);
				p.setAmount(amount);

			} else {
				System.out.println("Please send the correct taxid");
			}
		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();

		return p;
	}

}
